﻿using AutoMapper;
using Dapper;
using Max.PH6_2009A.LowCode.Domain;
using Max.PH6_2009A.LowCode.DTO;
using Max.PH6_2009A.LowCode.IRespository;
using Max.PH6_2009A.LowCode.IService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Max.PH6_2009A.LowCode.Service
{
    public class UserService : BaseService<UserModel>, IUserService
    {
        IUserRespository _respository;
        IUserRoleRespository _userRoleRespository;
        IMapper _map;
        IUnitOfWork _unitOfWork;
        IMenuService _menuService;
        public UserService(IUserRespository respository, IMapper map, IUserRoleRespository userRoleRespository, IUnitOfWork unitOfWork, IMenuService menuService) : base(respository)
        {
            _respository = respository;
            _map = map;
            _userRoleRespository = userRoleRespository;
            _unitOfWork = unitOfWork;
            _menuService = menuService;
        }


        #region 20230511

        public List<dynamic> GetMenus()
        {
            var result = this.GetMenus(0);
            return result;
        }

        private List<dynamic> GetMenus(int id)
        {
            string sql = "select * from [dbo].[tb_new_menu] where pid=@id";
            var _db = _unitOfWork.GetDbConnection();
            var list = _db.Query(sql, new { id = id });
            List<dynamic> result = new();
            list.ToList().ForEach(x => {

                var m = GetMenus(x.Id);
                result.Add(new
                {
                    id = x.Id,
                    pid = x.PId,
                    name = x.Name,
                    children = m == null || m.Count <= 0 ? null : m
                });
            });
            
            return result;
        }


        public int AddMenuByRoleID(int roleId,List<int> menuId)
        {
            //只添加角色对应的菜单，即只操作角色菜单的关联表
            var _db = _unitOfWork.GetDbConnection();
            _db.Open();
            using (var tran = _db.BeginTransaction())
            {
                try
                {
                    string sql = "delete from tb_new_role_menu where roleid=@roleid";

                    _db.Execute(sql, new { roleid = roleId }, tran);

                    sql = "insert into tb_new_role_menu(roleid,menuid) values(@roleid,@menuid)";

                    List<dynamic> list = new();
                    menuId.ForEach(x => {
                        list.Add(new { roleid = roleId,menuId = x});
                    });
                    _db.Execute(sql, list, tran);

                    tran.Commit();
                    return 1;
                }
                catch (Exception)
                {
                    tran.Rollback();
                    return -1;
                }
            }

            return -1;
        }


        #endregion
        public IEnumerable<UserOutputDto> Select(object param)
        {
            string sql = $"select * from tb_user";
            var data = _respository.Query(sql, param);
            //查询出来的结果result是IEnumerable<UserModel>
            //而我们想要的结果是IEnumerable<UserOutputDto>
            //所以，需要将结果转换为DTO
            var result = _map.Map<IEnumerable<UserModel>, IEnumerable<UserOutputDto>>(data);

            return result;
        }


        /// <summary>
        /// 添加用户的同时，添加所属的角色
        /// </summary>
        /// <param name="dto"></param>
        public void InsertUserAndRoles(UserInsertRoleDto dto)
        {
            var _db = _unitOfWork.GetDbConnection();
            _db.Open();
            using (var tran = _db.BeginTransaction())
            {
                try
                {
                    #region 业务处理

                    //添加用户
                    UserModel model = new UserModel();
                    model.LoginName = dto.LoginName;

                    string sql = "";
                    //是添加还是修改，根据传过来的用户ID（UserId）来决定
                    if (string.IsNullOrEmpty(dto.UserId))
                    {
                        //这是添加
                        model.Id = Guid.NewGuid();
                        model.Password = MD5Hash.Hash.Content("123456");
                        sql = "insert into tb_user(id,loginname,password) values(@id,@loginname,@password); ";
                        _db.Execute(sql, model, tran);

                        //如果主键是int类型
                        //sql = "insert into tb_user(id,loginname,password) values(@id,@loginname,'123456'); select cast(scope_identity() as int);";
                        //int id = _db.Query<int>(sql, model, tran).FirstOrDefault();

                    }
                    else
                    {
                        model.Id = new Guid(dto.UserId);
                    }

                    #region 如果表主键是Int

                    /*
                     * 如果表的主键是int类型，那么需要在插入的时候，获取到这个自增长的ID，才能给用户角色表
                     * 则只需要sql后面再加下面一名sql语句即可
                     * sql += "select cast(scope_identity() as int);"
                     * 在执行的时候
                     * int id = _conn.Query<int>(sql, model).FirstOrDefault();
                     * **/
                    #endregion

                    //添加之前，把这个用户对应的角色先全都删除，再进行添加
                    sql = "delete from tb_user_role where userid=@id";
                    _db.Execute(sql, model, tran);

                    //添加用户对应的角色（多个）
                    List<UserRoleModel> userRoles = new List<UserRoleModel>();
                    dto.RolesId.ForEach(x =>
                    {
                        userRoles.Add(new UserRoleModel
                        {
                            RoleId = x,
                            UserId = model.Id
                        });
                    });
                    sql = "insert into tb_user_role(userid,roleid) values(@userid,@roleid);";
                    _db.Execute(sql, userRoles, tran);

                    #endregion

                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                }
            }
        }

        /// <summary>
        /// 获取用户对应的角色列表
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public IEnumerable<string> GetUserRoleByUserId(string userId)
        {

            string sql = "select * from tb_user_role where userid=@userid";
            IEnumerable<UserRoleModel> data = _unitOfWork.GetDbConnection().Query<UserRoleModel>(sql, new { userid = userId });

            var result = data.Select(x => x.RoleId.ToString());

            return result;
        }

        //HttpClient
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public (bool, string, UserOutputDto) Login(string loginName, string password)
        {
            string sql = "select id from tb_user where loginname=@loginname and password =@password";
            var _db = _unitOfWork.GetDbConnection();
            password = MD5Hash.Hash.Content(password);
            var m = _db.Query<UserOutputDto>(sql, new { loginname = loginName, password = password }).FirstOrDefault();
            if (m == null)
            {
                //登录失败，原因：用户密码错误
                return (false, "用户名或密码错误", null);
            }
            else
            {
                return (true, "登录成功", m);
            }
        }

        /// <summary>
        /// 根据用户ID，获取它拥有的权限菜单
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public IEnumerable<MenuModel> GetUserMenu(string userId)
        {
            string sql = "pr_userlogin";
            Dapper.DynamicParameters parameters = new();
            parameters.Add("@id", userId);

            var result = _unitOfWork.GetDbConnection().Query<MenuModel>(sql, parameters, commandType: CommandType.StoredProcedure);
            return result;
        }

        //根据已有菜单，反递归获取这些菜单对应的父节点
        private void GetMenus(IEnumerable<MenuModel> data)
        {
            
        }


        /// <summary>
        /// 批量删除（删除用户的同时，删除该用户对应的角色（用户角色关联表）
        /// </summary>
        /// <param name="ls"></param>
        public new (bool, string) Delete(List<RbacDeleteByIdDto> ls)
        {
            string sql = "delete from tb_user where id=@id;delete from tb_user_role where userid=@id";
            //这么多条删除操作，需要采用事务
            var _db = _unitOfWork.GetDbConnection();
            _db.Open();
            using (var tran = _db.BeginTransaction())
            {
                try
                {
                    _db.Execute(sql, ls, tran);

                    tran.Commit();
                    return (true, "成功删除该用户及对应的角色");
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return (false, $"删除失败，原因：{ex.Message}");
                }
            }
        }


        public void InsertMoreEmployee(List<EmployeeModel> employees)
        {
            //var _sql = Common.ReflecationHelper.GetDapperInsertSqlString(employees);
            string sql = "insert into tb_employee(Name,Age,Gender,CardNo,HighestEduction,Native,Address,IsMarry,illHistory,Phone,EMail,WeiXin,QQ) " +
                "values(@Name,@Age,@Gender,@CardNo,@HighestEduction,@Native,@Address,@IsMarry,@illHistory,@Phone,@EMail,@WeiXin,@QQ)";

            var _db = _unitOfWork.GetDbConnection();
            _db.Open();
            using (var tran = _db.BeginTransaction())
            {
                try
                {
                    //employees 最开始获取excel 是43条
                    //现在数据库中有3条
                    //那么插入的时候，只能插入40条
                    var dbList = _db.Query<EmployeeModel>("select * from tb_employee",transaction:tran);
                    dbList.ToList().ForEach(x => {
                        var m = employees.Where(p => p.Name.Equals(x.Name)).FirstOrDefault();
                        employees.Remove(m);
                    });

                    //添加员工的时候，添加用户
                    List<UserModel> users = new();
                    employees.ForEach(x =>
                    {
                        //登录账号为身份证号，密码为身份证号后6位，并需要对密码进行密
                        UserModel user = new();
                        string card = x.CardNo;
                        user.LoginName = card;
                        user.Password = MD5Hash.Hash.Content(card.Substring(card.Length - 6, 6));// ;
                        users.Add(user);
                    });

                    _db.Execute(sql, employees, tran);

                    sql = "insert into tb_user(loginname,password) values(@loginname,@password)";
                    _db.Execute(sql, users, tran);

                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();
                }
            }
        }


        /// <summary>
        /// 获取所有员工信息
        /// </summary>
        /// <returns></returns>
        public List<EmployeeModel> GetEmployee()
        {
            var _db = _unitOfWork.GetDbConnection();
            var list = _db.Query<EmployeeModel>("select * from tb_employee");
            return list.ToList();
        }
    }
}
